#!/usr/local/bin/perl5

#$Id$

use strict;
use warnings;

use Carp;
use English qw(-no_match_vars);

use File::Basename;
use Net::Domain qw(hostname);
use Fcntl qw(:DEFAULT :flock);

use FixInc::Logger;
use FixInc::DBAccess;
use FixInc::OopOptsDB;

my $SCRIPT_NM = basename($0);
my $HOST_NM = hostname();

my $log = FixInc::Logger->new( 'msg_tag' => "$HOST_NM $PID", 'min_level' => 'info' );

my $optsets = FixInc::OopOptsDB->newOptSet(
    help => "This script prepares the candidates for OA/FI data comparison. It inserts perm_id/party_id pairs into oa_fi_int..entity_party_map_compare table.",

    defaults => {
    },

    optdefs => FixInc::OopOptsDB->newOptDef(
        {   'name'    => 'compare_mode',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'enum'    => ['research', 'refresh', 'research_parents'],
            'help'    => "Mode for comparison"
        },
        {   'name'    => 'obj_type_cd',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'enum'    => ['GCPA'],
            'help'    => "To distinguish parties for different database, e.g. GCPA is for Govcorp"
        },
        {   'name'    => 'oa_fi_comp_dir',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "Dir for storing any intermediate files"
        },
        {   'name'    => 'bcp_cmd',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "bcp command with full path"
        },
        {   'name'    => 'oa_feed_sr',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "Database server where OA Feed database sits"
        },
        {   'name'    => 'oa_feed_db',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "Database name for OA Feed database, it's oa_feed for now"
        },
        {   'name'    => 'oa_feed_un',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "User name for OA Feed database"
        },
        {   'name'    => 'oa_feed_pw',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "Password for OA Feed database"
        },
        {   'name'    => 'dbor_sr',
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "Database server where dbor_db resides"
        },
        {   'name'    => 'dbor_db',
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "For which database the comparison is for, it's dbor_govcorp for now"
        },
        {   'name'    => 'dbor_un',
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "User name for dbor_db"
        },
        {   'name'    => 'dbor_pw',
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "Password for dbor_db"
        },
        {   'name'    => 'joint_sr',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "Database server which contains both Entity database and Govcorp database"
        },
        {   'name'    => 'joint_db',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "Name of database used in joint server for temporary storage"
        },
        {   'name'    => 'joint_sr_un',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "User name for joint server"
        },
        {   'name'    => 'joint_sr_pw',
            'required' => 1,
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'string',
            'help'    => "Password for joint server"
        },
        {   'name'    => 'comp_batch_rowcount',
            'minargs' => 1,
            'maxargs' => 1,
            'type'    => 'count',
            'help'    => "Will not be used in this script"
        },
    )
);

my $oops = FixInc::OopOptsDB->new(
    optset => $optsets,
    log => $log
) or $log->die("Error when parsing parameters");

$log->notice( "=== $SCRIPT_NM has started === ( $HOST_NM, PID: $PID )" );

# As usual, get the big O
#
my $O = $oops->opthash;

# Set log_level,log_mail,log_stderr to logger if defined
# Just know: it's done implicitly in FixInc::OopOptsDB
#
# $log->paramApply($O);

# Make sure dir exists
#
my $working_dir = $O->{oa_fi_comp_dir};
-e $working_dir or $log->die("Dir '$working_dir' not exist");

my $obj_type_cd = $O->{obj_type_cd};

# Collision detection
#
my $FH_FILE = "$working_dir/flock_$obj_type_cd.fh";
sysopen(FH, $FH_FILE, O_WRONLY|O_CREAT) or $log->die("Can't open $FH_FILE: $OS_ERROR");
if (!flock(FH, LOCK_EX|LOCK_NB))
{
    $log->die("Collision detected: another instance is running.");
}

truncate(FH, 0);
print FH $O->{compare_mode};

$log->notice("Mode for comparison: $O->{compare_mode}");

my $oa_dbh = FixInc::DBAccess->connect(
    server   => $O->{oa_feed_sr},
    username => $O->{oa_feed_un},
    password => $O->{oa_feed_pw},
    database => 'tempdb',
    log      => $log,
) or $log->die("Can't connect to database: $FixInc::DBAccess::errstr");

# Make sure bcp command exists
my $bcp_cmd = $O->{bcp_cmd};
-e $bcp_cmd or $log->die("Bcp command '$bcp_cmd' not exist");

my $joint_dbh = FixInc::DBAccess->connect(
    server   => $O->{joint_sr},
    username => $O->{joint_sr_un},
    password => $O->{joint_sr_pw},
    database => $O->{joint_db},
    log      => $log,
) or $log->die("Can't connect to database: $FixInc::DBAccess::errstr");

my $entity_party_pair_comp = 'entity_party_pair_comp_' . $PID;

my $drop_table_sql =<<"END_SQL";
    IF EXISTS (SELECT name FROM sysobjects WHERE type = 'U' AND name = '$entity_party_pair_comp')
    DROP TABLE $entity_party_pair_comp
END_SQL

$joint_dbh->do($drop_table_sql);

my $join_sql =<<"END_SQL";
    select
        cast(e.entity_permid as numeric(20)) entity_permid,
        cast(p.party_id as binary(8)) party_id, '$obj_type_cd' obj_type_cd
    into $entity_party_pair_comp
    from data_entity..entity e inner join data_govcorp..party p on p.issuer_orgid = e.rcp_orgid
    where e.duplicate_of_id is null and e.duplicate_of_nacode is null
END_SQL

my $cnt = $joint_dbh->do($join_sql);

$log->notice("Totally, $cnt entity party pair(s)");

my $del_dup_sql =<<"END_SQL";
    -- Delete duplicate rows
    delete $entity_party_pair_comp where party_id in (
        select party_id from $entity_party_pair_comp
        group by party_id having count(*) > 1
    )
END_SQL

$joint_dbh->do($del_dup_sql);

# Bcp out entity party pairs
#
my $bcp_out_cmd =
    "$bcp_cmd $O->{joint_db}..$entity_party_pair_comp out $working_dir/entity_party_pair_comp_$PID.bcp " .
    "-c -t\\| -S $O->{joint_sr} -U $O->{joint_sr_un} -P $O->{joint_sr_pw} 2>&1";

my $std_out = `$bcp_out_cmd`;    # Run the command

# OS level Error
#
if ($CHILD_ERROR == -1)
{
    $log->die("bcp out failed, system error: " . $OS_ERROR);
}

# Command failed for other reasons
# e.g. bad command, database error, disk full
#
if ($CHILD_ERROR != 0)
{
    $log->die("bcp out failed, $std_out");
}

$joint_dbh->do($drop_table_sql);

$joint_dbh->disconnect();    # Release as early as possible

my $clear_data_sql =<<"END_SQL";
    delete oa_fi_int..entity_party_map_assist
    where obj_type_cd = '$obj_type_cd'
END_SQL

$oa_dbh->do($clear_data_sql);

my $bcp_in_cmd =
    "$bcp_cmd oa_fi_int..entity_party_map_assist in $working_dir/entity_party_pair_comp_$PID.bcp " .
    "-c -t\\| -S $O->{oa_feed_sr} -U $O->{oa_feed_un} -P $O->{oa_feed_pw} 2>&1";

$std_out = `$bcp_in_cmd`;

# OS level Error
#
if ($CHILD_ERROR == -1)
{
    $log->die("bcp in failed, system error: " . $OS_ERROR);
}

# Command failed for other reasons
# e.g. bad command, database error, file not found
#
if ($CHILD_ERROR != 0)
{
    $log->die("bcp in failed, $std_out");
}

# Remove bcp file
#
unlink("$working_dir/entity_party_pair_comp_$PID.bcp");

my $candidate_sql =<<"END_SQL";
    delete oa_fi_int..entity_party_map_compare
    where obj_type_cd = '$obj_type_cd'
END_SQL

if ($O->{compare_mode} eq 'refresh')
{
    $candidate_sql .=<<"END_SQL";

    insert into oa_fi_int..entity_party_map_compare
    select OrganizationId, party_id, obj_type_cd, 'n'
    from oa_fi_int..entity_party_map
    where obj_type_cd = '$obj_type_cd'
END_SQL
}
elsif ($O->{compare_mode} eq 'research')    # 'research'
{
    $candidate_sql .=<<"END_SQL";

    insert into oa_fi_int..entity_party_map_compare
    select OrganizationId, party_id, obj_type_cd, 'n'
    from oa_fi_int..entity_party_map_assist a
    where a.obj_type_cd = '$obj_type_cd'
    and not exists (
        select 1 from oa_fi_int..entity_party_map
        where party_id = a.party_id
    )
END_SQL
}
else    # 'Research Parents'
{
    $candidate_sql .=<<"END_SQL";

    insert into oa_fi_int..entity_party_map_compare
    select OrganizationId, party_id, obj_type_cd, 'n'
    from oa_fi_int..entity_party_map_assist a
    where a.obj_type_cd = '$obj_type_cd'
    and not exists (
        select 1 from oa_fi_int..entity_party_map
        where party_id = a.party_id
    )
    UNION
    select OrganizationId, party_id, obj_type_cd, 'n'
    from oa_fi_int..entity_party_map
    where obj_type_cd = '$obj_type_cd' and load_mode = 'DEF'
END_SQL
}

$cnt = $oa_dbh->do($candidate_sql);

$log->notice("$cnt row(s) inserted into entity_party_map_compare");

$oa_dbh->disconnect();

$log->notice("=== $SCRIPT_NM finished successfully ===");

exit 0;

END
{
    flock(FH, LOCK_UN);
    close(FH);
}
